package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.junit.Test;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

/**
 * 员工表查询
 */
public class SearchEmployee {
    /**
     * 需求1: 查询所有的员工信息 (不包含没有部门的员工)
     * @throws SQLException
     */
    @Test
    public void searchNoneDept() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select * from employee where did is not null;";
        List<Object[]> employees = queryRunner.query(sql, new ArrayListHandler());
        for (Object[] objects :employees){
            System.out.println(Arrays.toString(objects));
        }    }

    @Test
    public void searchEveryone() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "SELECT \n" +
                "\tNAME AS '姓名',\n" +
                "\tsalary AS '薪水',\n" +
                "\tdeptname AS '部门名称'  \n" +
                "\tFROM employee e LEFT JOIN dept d ON e.`did` = d.`id`; ";
        List<Object[]> query = queryRunner.query(sql, new ArrayListHandler());
        for (Object[] objects :query){
            System.out.println(Arrays.toString(objects));
        }
    }
}
